DataFrame Join Operations

Joining multiple datasets in PySpark using standard SQL join types like inner, left, right, outer, semi, and anti joins.

What is the Join Operation?

The join() operation merges two DataFrames horizontally based on a matching key or expression, equivalent to relational database tables joins.

It is a critical transformation in relational modeling and distributed pipelines, where join types determine how unmatched records are preserved.

Syntax and Supported Join Types

# Standard Syntax
df_left.join(df_right, joinExpression, joinType)

Supported Join Types in PySpark:

• "inner" (Default): Keeps only rows with keys matching in both sides.
• "left" or "left_outer": Keeps all left rows, filling unmatched right side with nulls.
• "right" or "right_outer": Keeps all right rows, filling unmatched left side with nulls.
• "outer" or "full": Keeps all rows from both sides, aligning keys and using nulls for gaps.
• "left_semi": Keeps left rows that have a match in the right side (keeps only left columns).
• "left_anti": Keeps left rows that DO NOT have any match in the right side (ideal for finding missing/deleted keys).

Example Usage Pipeline

Below is a complete, copy-paste-ready PySpark script demonstrating joins:

from pyspark.sql import SparkSession
from pyspark.sql import functions as F

# 1. Setup local Spark session
spark = SparkSession.builder \
    .appName("DataFrame Joins Demo") \
    .master("local[*]") \
    .getOrCreate()

# 2. Dummy Left Dataset (Employees)
emp_data = [
    (1, "Alice", 101),
    (2, "Bob", 102),
    (3, "Charlie", 103),  # Dep ID 103 does not exist
    (4, "David", None),   # No department assigned
]
emp_columns = ["emp_id", "emp_name", "dept_id"]
emp_df = spark.createDataFrame(emp_data, emp_columns)

# 3. Dummy Right Dataset (Departments)
dept_data = [
    (101, "Engineering"),
    (102, "Marketing"),
    (104, "Finance"),      # No employees in Finance
]
dept_columns = ["dept_id", "dept_name"]
dept_df = spark.createDataFrame(dept_data, dept_columns)

# 4. Perform Inner Join (keeps only matching employees + depts)
inner_join = emp_df.join(dept_df, "dept_id", "inner")

# 5. Perform Left Join (keeps all employees, unmatched depts get Null)
left_join = emp_df.join(dept_df, "dept_id", "left")

# 6. Perform Left Anti Join (finds employees in departments that DO NOT exist or are null)
unmatched_employees = emp_df.join(dept_df, "dept_id", "left_anti")

# 7. Show results
print("=== Inner Join ===")
inner_join.show()

print("=== Left Outer Join ===")
left_join.show()

print("=== Left Anti Join (Orphaned Employees) ===")
unmatched_employees.show()

Rendered Output:

=== Inner Join ===
+-------+------+--------+-----------+
|dept_id|emp_id|emp_name|  dept_name|
+-------+------+--------+-----------+
|    101|     1|   Alice|Engineering|
|    102|     2|     Bob|  Marketing|
+-------+------+--------+-----------+

=== Left Outer Join ===
+-------+------+--------+-----------+
|dept_id|emp_id|emp_name|  dept_name|
+-------+------+--------+-----------+
|    101|     1|   Alice|Engineering|
|    102|     2|     Bob|  Marketing|
|    103|     3| Charlie|       null|
|   null|     4|   David|       null|
+-------+------+--------+-----------+

=== Left Anti Join (Orphaned Employees) ===
+------+--------+-------+
|emp_id|emp_name|dept_id|
+------+--------+-------+
|     3| Charlie|    103|
|     4|   David|   null|
+------+--------+-------+